/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import com.gargoylesoftware.htmlunit.BrowserVersion;
import com.gargoylesoftware.htmlunit.FailingHttpStatusCodeException;
import com.gargoylesoftware.htmlunit.HttpMethod;
import com.gargoylesoftware.htmlunit.WebClient;
import com.gargoylesoftware.htmlunit.WebRequest;
import com.gargoylesoftware.htmlunit.html.HtmlElement;
import com.gargoylesoftware.htmlunit.html.HtmlPage;
import com.gargoylesoftware.htmlunit.html.HtmlTable;
import com.gargoylesoftware.htmlunit.html.HtmlTableCell;
import com.gargoylesoftware.htmlunit.util.NameValuePair;

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Catalin Dincu
 */
public class InfoferInterraction {
    private List<Integer> trains;
    private String url;
    
	public void testURLConnection() throws FailingHttpStatusCodeException, IOException{
    	WebClient client = new WebClient();   	
    	//client.setThrowExceptionOnFailingStatusCode(false);
    	client.setJavaScriptEnabled(false);
    	client.setCssEnabled(false);
    	//HtmlPage page = client.getPage("http://www.infofer.ro/");
		WebRequest requestSettings = new WebRequest(new URL("http://www.infofer.ro/PlSo.asp"), HttpMethod.POST);
		//PlSo   ISta
    			// Then we set the request parameters
    			requestSettings.setRequestParameters(new ArrayList<NameValuePair>());
    			requestSettings.getRequestParameters().add(new NameValuePair("vbSta", "1"));
    			requestSettings.getRequestParameters().add(new NameValuePair("idSta", "80892"));
    			requestSettings.getRequestParameters().add(new NameValuePair("cboData", "1/18/2013"));
    			requestSettings.getRequestParameters().add(new NameValuePair("tip", "1"));
    			requestSettings.getRequestParameters().add(new NameValuePair("SubmitStatii", "Cauta"));
    			requestSettings.getRequestParameters().add(new NameValuePair("lng", "1"));
    			
    			//String firstPart = "http://www.infofer.ro/ISta.asp?vbSta=0&idSta=";
//        		String lastPart = "&cboData=7%2F26%2F2011&tip=0&SubmitStatii=Cauta&lng=1";
    			
    			// Finally, we can get the page
    	//System.out.println(page.asText());
    			HtmlPage page = client.getPage(requestSettings);
    			System.out.println(page.asText());
    			//System.out.println(requestSettings.getRequestParameters());
    			//System.out.println(page.getDocumentElement());
    }

    public String setStationIds(String stationName, String finalCity) throws FailingHttpStatusCodeException, MalformedURLException, IOException{
    	//creating the URL request for the destination page
    	WebClient client = new WebClient();
    	client.setJavaScriptEnabled(false);
    	client.setCssEnabled(false);
    	WebRequest requestSettings = new WebRequest(new URL("http://www.infofer.ro/ISta.asp"), HttpMethod.POST);
    	requestSettings.setRequestParameters(new ArrayList<NameValuePair>());
		requestSettings.getRequestParameters().add(new NameValuePair("vbSta", "1"));
		requestSettings.getRequestParameters().add(new NameValuePair("idSta", finalCity));
		requestSettings.getRequestParameters().add(new NameValuePair("cboData", "1/18/2013")); // ***************************this line needs to be changed to the current date, when updating DB************************
		requestSettings.getRequestParameters().add(new NameValuePair("tip", "1"));
		requestSettings.getRequestParameters().add(new NameValuePair("SubmitStatii", "Cauta"));
		requestSettings.getRequestParameters().add(new NameValuePair("lng", "1"));
    	HtmlPage page = client.getPage(requestSettings);
    	String returnStatement = "";
    	//String returnStatement = "Inserting stationId for: " + stationName;
    	
    	Boolean isStation = true;
    	try{
    		HtmlElement stationId = (HtmlElement) page.getByXPath("/html/body/form/table[2]/tbody/tr/td[2]/table[4]/tbody/tr/td[2]/select/option").get(0);
    		if(Integer.parseInt(stationId.getAttribute("value")) < 10) isStation = false;
    		//System.out.println(stationId.getAttribute("value"));
    	}catch(Exception e){
    		isStation = false;
    	}
    	
    	if(isStation){
    		HtmlElement stationId = (HtmlElement) page.getByXPath("/html/body/form/table[2]/tbody/tr/td[2]/table[4]/tbody/tr/td[2]/select/option").get(0);
        	//System.out.println(stationId.getAttribute("value"));
        	
        	String infoferStationId = stationId.getAttribute("value");
        	
        	Connection con = null;
        	try{
        		Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");

                PreparedStatement query = (PreparedStatement) con.prepareStatement("insert into InfoferStations(stationName, stationId) values(?,?)");
                query.setString(1, stationName);
                query.setString(2, infoferStationId);
                query.executeUpdate();
        	}
        	catch(Exception e){
        		System.out.println(e.getMessage());
        	}
        	finally{
                try {
                        if(con != null)
                                con.close();
                }
                catch(Exception e) {}
        	}
        	returnStatement = "Inserted value = " + infoferStationId + "     for station: " + stationName;
    	}
    	
    	return returnStatement;
    }
    
    public ArrayList<String> getStationIds(){
    	ArrayList<String> stationIds = new ArrayList<String>();
    	Connection con = null;
        try{
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");

                PreparedStatement query = (PreparedStatement) con.prepareStatement("select stationId from InfoferStations");
                ResultSet rs = query.executeQuery();
                while(rs.next()){
                	stationIds.add(rs.getString(1));
                }
        }
        catch (Exception e){
                System.out.println(e.getMessage());
        }
        finally{
                try {
                        if(con != null)
                                con.close();
                }
                catch(Exception e) {}
        }
        return stationIds;
    }
    
    public ArrayList<String> getTrainNumbers(){
    	ArrayList<String> trainNumbers = new ArrayList<String>();
    	Connection con = null;
    	try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");

            PreparedStatement query = (PreparedStatement) con.prepareStatement("select trainNumber from InfoferTrains order by trainNumber asc");
            ResultSet rs = query.executeQuery();
            while(rs.next()){
            	trainNumbers.add(rs.getString(1));
            }
    }
    catch (Exception e){
            System.out.println(e.getMessage());
    }
    finally{
            try {
                    if(con != null)
                            con.close();
            }
            catch(Exception e) {}
    }
    return trainNumbers;
    }
    
	public ArrayList<String> getStationNames(){
    	Connection con = null;
    	ArrayList<String> stationNames = new ArrayList<String>();
    	try{
    		Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");
            
            PreparedStatement query = (PreparedStatement) con.prepareStatement("select distinct(stationName) from TrainStations order by stationName");
            ResultSet rs = query.executeQuery();
            
            while(rs.next()){
            	stationNames.add(rs.getString(1));
            }
    	}
    	catch(Exception e){
    		System.out.println(e.getMessage());
    	}
    	finally{
    		try{
    			if(con != null) con.close();
    		}
    		catch(Exception e){}
    	}
    	return stationNames;
    }
    
	public ArrayList<String> findAdditionalStations(String url) throws FailingHttpStatusCodeException, MalformedURLException, IOException{
		WebClient client = new WebClient();
		HtmlPage page = client.getPage(url);
		
		HtmlTable stationsTable = (HtmlTable) page.getByXPath("/html/body/table").get(0);
		Integer rowCount = stationsTable.getRowCount();
		//System.out.println(rowCount);
		ArrayList<String> infoferStations = new ArrayList<String>();
		for(int i = 0; i< rowCount; i++){
			infoferStations.add(stationsTable.getCellAt(i, 0).getTextContent().trim());
			infoferStations.add(stationsTable.getCellAt(i, 1).getTextContent().trim());
			infoferStations.add(stationsTable.getCellAt(i, 2).getTextContent().trim());
			infoferStations.add(stationsTable.getCellAt(i, 3).getTextContent().trim());
			infoferStations.add(stationsTable.getCellAt(i, 4).getTextContent().trim());
		}
		//infoferStations.remove(2099);
		//System.out.println(infoferStations.get(2098));
		return infoferStations;
	}

	public void insertTrainNumbers(String url) throws FailingHttpStatusCodeException, MalformedURLException, IOException{
		WebClient client = new WebClient();
		HtmlPage page = client.getPage(url);
		
		HtmlTable trainsInStation = (HtmlTable) page.getByXPath("/html/body/table[2]/tbody/tr/td[2]/table[6]").get(0);
		Integer rowCount = trainsInStation.getRowCount();
		ArrayList<String> trains = new ArrayList<String>();
		for(int i = 1; i <= rowCount-2; i += 2){
			//System.out.println(trainsInStation.getCellAt(i, 2).getTextContent().trim());
			trains.add(trainsInStation.getCellAt(i, 2).getTextContent().trim());
		}
		
		if(trains.size() > 0){
			for(String train : trains){
				Connection con = null;
                try{
                        Class.forName("com.mysql.jdbc.Driver");
                        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");

                        PreparedStatement query = (PreparedStatement) con.prepareStatement("insert into InfoferTrains(trainNumber) values(?)");
                        query.setString(1, train);
                        query.executeUpdate();
                        System.out.println("Inserted trainNumber: " + train);
                }
                catch (Exception e){
                        System.out.println(e.getMessage());
                }
                finally{
                        try {
                                if(con != null)
                                        con.close();
                        }
                        catch(Exception e) {}
                }
			}
		}
	}
    
	public void databaseOperations(String url) throws MalformedURLException, IOException{
        WebClient client = new WebClient();
        HtmlPage page = client.getPage(url);

        Boolean isTrain = true; 
        try{        
        	page.getByXPath("/html/body/table[2]/tbody/tr[3]/td[2]/table/tbody/tr[3]/td/table").get(0);
        }catch(IndexOutOfBoundsException e){
        	isTrain=false;        	
        }
        
        if(isTrain){
        	System.out.println("...populating database for the train");
	        // populate the Train table
	        HtmlTable trainTable = (HtmlTable) page.getByXPath("/html/body/table[2]/tbody/tr[3]/td[2]/table/tbody/tr[3]/td/table").get(0);
	        List<String> trainAttributes = new ArrayList<String>();
	        trainAttributes.add(trainTable.getCellAt(1, 2).getTextContent().trim()); //trainNumber
	        trainAttributes.add(trainTable.getCellAt(1, 1).getTextContent().trim()); //description
	        trainAttributes.add(trainTable.getCellAt(1, 0).getTextContent().trim()); //trainType
	
	        String hasRestrictions = null;
	        try{
	            if(trainTable.getCellAt(1, 5).getTextContent().trim() != null){
	                String link = trainTable.getCellAt(1, 5).getChildElements().iterator().next().getAttributes().item(0).getTextContent();
	                int egal = link.indexOf("=");
	                int and = link.indexOf("&");
	                hasRestrictions = link.substring(egal + 1, and);
	            }
	            else hasRestrictions = null;
	        }catch(NullPointerException e){
	        }finally{
	            //System.out.println("restrictie: " + hasRestrictions);
	        }
	        
	        Integer insertedTrainId = this.insertIntoTrainTable(trainAttributes);
	        //System.out.println(trainAttributes);
	
	        //populate the TrainStations table
	        HtmlTable trainStationsTable = (HtmlTable) page.getByXPath("/html/body/table[2]/tbody/tr[3]/td[2]/table[3]").get(0);
	        Integer rowCount = trainStationsTable.getRowCount();
	        //System.out.println(rowCount);
	        for(int i = 1; i<= rowCount-2; i += 2){
	            List<String> trainStation = new ArrayList<String>();
	            trainStation.add(trainStationsTable.getCellAt(i, 0).getTextContent().trim()); //km
	            trainStation.add(trainStationsTable.getCellAt(i, 2).getTextContent().trim()); //stationName
	            trainStation.add(trainStationsTable.getCellAt(i, 4).getTextContent().trim()); //arrival
	            trainStation.add(trainStationsTable.getCellAt(i, 8).getTextContent().trim()); //departure
	            this.insertIntoTrainStationsTable(insertedTrainId, trainStation);
	        }
	
	        //populate the TrainServices table
	        HtmlTableCell trainServices = (HtmlTableCell) page.getByXPath("/html/body/table[2]/tbody/tr[3]/td[2]/table[3]/tbody/tr[2]/td[11]").get(0);
	        Iterable<HtmlElement> childs = trainServices.getChildElements();
	        List<String> services = new ArrayList<String>();
	        while(childs.iterator().hasNext()){
	        	services.add(childs.iterator().next().getAttributes().item(3).getTextContent().trim());
	        	childs.iterator().next().remove();
	        }
	        for(int i = 0; i< services.size(); i++){
	        	if(services.get(i).equals("Clasa 1")) services.set(i, "1");
	        	else if(services.get(i).equals("Clasa 2")) services.set(i, "2");
	        	else if(services.get(i).equals("Dormit 1")) services.set(i, "3");
	        	else if(services.get(i).equals("Dormit 2")) services.set(i, "4");
	        	else if(services.get(i).equals("Restaurant/bar")) services.set(i, "5");
	        	else if(services.get(i).equals("Business Standard")) services.set(i, "6");
	        	else if(services.get(i).equals("Business Exclusiv")) services.set(i, "7");
	        	else if(services.get(i).equals("Catering")) services.set(i, "8");
	        	else if(services.get(i).equals("Auto")) services.set(i, "9");
	        	else if(services.get(i).equals("Biciclete")) services.set(i, "10");
	        	else if(services.get(i).equals("Cuseta 4")) services.set(i, "11");
	        	else if(services.get(i).equals("Cuseta 6")) services.set(i, "12");
	        	else services.set(i, "-1");
	        }
	        for(String service : services){
	        	this.insertIntoTrainServicesTable(insertedTrainId, service);
	        }
	        //System.out.println(trainServices.getChildElements().iterator().next().getAttributes().item(3).getTextContent());
	        
	        //populate the TrainRestrictions table
	        if(hasRestrictions != null){
	            String urlRestriction = "http://www.mersultrenurilorcfr.ro/rct.asp?idtrv=" + hasRestrictions + "&cbodata=12/18/2011&lng=1";
	            this.restrictionOperations(insertedTrainId, urlRestriction);    
	        }
	        
	        client.closeAllWindows();
        }
    }

    public void restrictionOperations(Integer trainId, String url) throws IOException{
        WebClient clientRestriction = new WebClient(BrowserVersion.INTERNET_EXPLORER_8);
        clientRestriction.setThrowExceptionOnFailingStatusCode(false);
        HtmlPage pageRestriction = clientRestriction.getPage(url);
        HtmlTable trainRestrictionsTable = (HtmlTable) pageRestriction.getByXPath("/html/body/table[4]").get(0);
        for(int i = 1; i < 13; i++){
        	for(int j = 1;j < 32; j++){
        		if(trainRestrictionsTable.getCellAt(i, j).getAttribute("bgcolor").equals("#E10027")){
        			//System.out.println("2011-" + i + "-" + j);
        			String restrictionDate = "2011-" + i + "-" + j;
        			this.insertIntoTrainRestrictionsTable(trainId, restrictionDate);
        		}
        	}
        }
            
        clientRestriction.closeAllWindows();
    }
    
	public Integer insertIntoTrainTable(List<String> trainAttributes){
        //database connection crap
        Connection con = null;
                try{
                        Class.forName("com.mysql.jdbc.Driver");
                        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");

                        PreparedStatement query = (PreparedStatement) con.prepareStatement("insert into Train(trainNumber, description, trainType, isValid) values(?,?,?,true)", PreparedStatement.RETURN_GENERATED_KEYS);
                        query.setString(1, trainAttributes.get(0));
                        query.setString(2, trainAttributes.get(1));
                        query.setString(3, trainAttributes.get(2));
                        Integer result = query.executeUpdate();
                        if(result == 1){
                            ResultSet rs = query.getGeneratedKeys();
                            while(rs.next()){
                                return rs.getInt(1);
                            }
                        }
                }
                catch (Exception e){
                        System.out.println(e.getMessage());
                }
                finally{
                        try {
                                if(con != null)
                                        con.close();
                        }
                        catch(Exception e) {}                        
                }
                return 0;
    }

    public void insertIntoTrainStationsTable(Integer trainNumber, List<String> trainStations){
        Connection con = null;
                try{
                        Class.forName("com.mysql.jdbc.Driver");
                        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");

                        PreparedStatement query = (PreparedStatement) con.prepareStatement("insert into TrainStations(trainId, stationName, km, arrival, departure) values(?,?,?,?,?)");
                        query.setInt(1, trainNumber);
                        query.setString(2, trainStations.get(1));
                        query.setString(3, trainStations.get(0));
                        query.setString(4, trainStations.get(2));
                        query.setString(5, trainStations.get(3));
                        query.executeUpdate();
                }
                catch (Exception e){
                        System.out.println(e.getMessage());
                }
                finally{
                        try {
                                if(con != null)
                                        con.close();
                        }
                        catch(Exception e) {}
                }
    }

    public void insertIntoTrainServicesTable(Integer trainNumber, String service){
    	Connection con = null;
        try{
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");

                PreparedStatement query = (PreparedStatement) con.prepareStatement("insert into TrainServices(trainId, serviceId) values(?,?)");
                query.setInt(1, trainNumber);
                query.setInt(2, Integer.parseInt(service));
                query.executeUpdate();
        }
        catch (Exception e){
                System.out.println(e.getMessage());
        }
        finally{
                try {
                        if(con != null)
                                con.close();
                }
                catch(Exception e) {}
        }
    }
    
    public void insertIntoTrainRestrictionsTable(Integer trainNumber, String restrictionDate){
    	Connection con = null;
        try{
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");

                PreparedStatement query = (PreparedStatement) con.prepareStatement("insert into TrainRestrictions(trainId, restrictionDate) values(?,?)");
                query.setInt(1, trainNumber);
                query.setString(2, restrictionDate);
                query.executeUpdate();
        }
        catch (Exception e){
                System.out.println(e.getMessage());
        }
        finally{
                try {
                        if(con != null)
                                con.close();
                }
                catch(Exception e) {}
        }
    }
    
    public List<String> trainsList(){
    	Connection con = null;
    	List<String> trainsList = new ArrayList<String>();
        try{
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/RoWAY","root", "");

                PreparedStatement query = (PreparedStatement) con.prepareStatement("select distinct(nrTren) from magistrale");
                ResultSet result = query.executeQuery();
                
                while(result.next()){
                	trainsList.add(result.getString(1));
                }
        }
        catch (Exception e){
                System.out.println(e.getMessage());
        }
        finally{
                try {
                        if(con != null)
                                con.close();
                }
                catch(Exception e) {}
        }
        return trainsList;
    }
    
    public void setTrains(List<Integer> trains){
        this.trains = trains;
    }
    public List<Integer> getTrains(){
        return this.trains;
    }
    public void setUrl(String url){
        this.url = url;
    }
    public String getUrl(){
        return this.url;
    }
    
    //main
    public static void main(String[] args) throws MalformedURLException, IOException {
        InfoferInterraction conn = new InfoferInterraction();
        
        //conn.testURLConnection();
        //insert into the database all trainNumbers from Romania considering all the stations
//        ArrayList<String> stationIds = new ArrayList<String>();
//        stationIds = conn.getStationIds();
//        for(String stationId : stationIds){
//        	System.out.println("Statia: " + stationId);
//        	String firstPart = "http://www.infofer.ro/PlSo.asp?vbSta=1&idSta=";
//        	String lastPart = "&cboData=6%2F30%2F2011&tip=0&SubmitStatii=Cauta&lng=1";
//        	String url = firstPart + stationId + lastPart;
//        	conn.insertTrainNumbers(url);
//        }
        
        
//        //insert the stations which don't have trains ------------------------------------------> SECOND OPERATION
//        ArrayList<String> additionalStations = new ArrayList<String>();
//        additionalStations = conn.findAdditionalStations("http://merstrenuri.ro/?Sub=Help");
//        System.out.println(additionalStations);
//        //insert the infoferStationIds for stations which don't have trains
//        ArrayList<String> stationNames = new ArrayList<String>();
//        stationNames = conn.getStationNames();
//        for(String additionalStation : additionalStations){
//        	if(!stationNames.contains(additionalStation)){
//        		//System.out.println(additionalStation);
//        		String temp = additionalStation.replace("+", "%2B");
//        		String finalCity = temp.replace(" ", "+");
//        		System.out.println(conn.setStationIds(additionalStation, finalCity));
//        	}
//        }
        
//        String additionalStation = "Timisoara CET";
//        String firstPart = "http://www.infofer.ro/ISta.asp?vbSta=0&idSta=";
//		String lastPart = "&cboData=7%2F26%2F2011&tip=0&SubmitStatii=Cauta&lng=1";
//		String temp = additionalStation.replace("+", "%2B");
//		String finalCity = temp.replace(" ", "+");
//		String url = firstPart + finalCity + lastPart;
//		System.out.println(conn.setStationIds(additionalStation, url));
        
        //insert the infoferStationIds in the database ----------------------------------------> FIRST OPERATION
        ArrayList<String> stationNames = new ArrayList<String>();
        stationNames = conn.getStationNames();
        for(String station : stationNames){
//        	String temp = station.replace("+", "%2B");
//        	String finalCity = temp.replace(" ", "+");
        	System.out.println(conn.setStationIds(station, station));
        }


//        ArrayList<String> trainNumbers = new ArrayList<String>();
//        trainNumbers = conn.getTrainNumbers();
//        //System.out.println(trainNumbers);
//        for(String trainNumber : trainNumbers){
//        	//System.out.println(train);
//        	String firstPart = "http://infofer.ro/Trs.asp?idTrv=";
//        	String lastPart = "&cboData=6%2F28%2F2011&SubmitTrn=Cauta&tip=infotrains&lng=1";
//        	String url = firstPart + trainNumber + lastPart;
//        	conn.setUrl(url);
//        	System.out.println("Trying trainNumber: " + trainNumber);
//        	conn.databaseOperations(conn.getUrl());
//        	//System.out.println(train);
//        }
    }
}
